﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dapper;
using System.Data;

namespace Utils
{

    public static class DataBaseUtility
    {
        public static readonly string defaultConnection = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["defaultConnection"].ToString();

        static DataBaseUtility()
        {
            if (string.IsNullOrEmpty(defaultConnection))
                throw new ArgumentNullException("默认连接字符串不能为空");
        }


        #region 基于Dapper的封装方法


        public static T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var connection = new MySqlConnection(defaultConnection))
            {
                try
                {
                    connection.Open();
                    return connection.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType);
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行SQL语句,返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var connection = new MySqlConnection(defaultConnection))
            {
                try
                {
                    connection.Open();
                    return connection.Execute(sql, param);
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 查询并返回第一个结果
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirst<T>(string sql, object param = null)
        {
            using (var connection = new MySqlConnection(defaultConnection))
            {
                try
                {
                    connection.Open();
                    var ret = connection.QueryFirst<T>(sql, param);
                    return ret;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public static List<T> Query<T>(string sql, object param = null)
        {

            using (var connection = new MySqlConnection(defaultConnection))
            {
                try
                {
                    connection.Open();
                    var list = connection.Query<T>(sql, param);
                    if (list != null)
                        return list.ToList<T>();
                    return null;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public static List<TReturn> Query<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null)
        {
            using (var connection = new MySqlConnection(defaultConnection))
            {
                try
                {
                    connection.Open();
                    var list = connection.Query<TFirst, TSecond, TReturn>(sql, map, param);
                    if (list != null)
                        return list.ToList<TReturn>();
                    return null;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public static List<TReturn> Query<TFirst, TSecond, TThird, TReturn>(string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null)
        {
            using (var connection = new MySqlConnection(defaultConnection))
            {
                try
                {
                    connection.Open();
                    var list = connection.Query<TFirst, TSecond, TThird, TReturn>(sql, map, param);
                    if (list != null)
                        return list.ToList<TReturn>();
                    return null;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        #endregion

        #region 原生DataReader方法
        public static List<TReturn> Query<TReturn>(string sql, Func<MySqlDataReader, TReturn> map, MySqlParameter[] param = null)
        {
            var list = new List<TReturn>();
            using (var dr = MySqlHelper.ExecuteReader(defaultConnection, sql, param))
            {
                while (dr.Read())
                {
                    TReturn t = map(dr);
                    if (t != null)
                        list.Add(t);
                }
            }

            return list;
        }
        #endregion
    }
}